﻿Imports System
Imports System.Text
Imports System.Linq
Imports System.Xml
Imports System.Text.RegularExpressions

Public Class clsXML2SQL
    Public Function GetSQLfromXML(ByVal XMLString As String) As String
        Dim S As New clsSentence
        Dim Answer As String = ""
        Dim XLTree As New Xml.XmlDocument
        XLTree.LoadXml(XMLString)
        Dim NList As New ListView
        Dim NVal As New ListView
        Dim SelectedNN As New ListView
        Dim LCXMLTree As String = XMLString.ToLower.ToString
        Dim Trap As Integer = 0
        Dim Start As Integer = 0
        Dim d As XmlParserContext
        Dim X As New Xml.XmlTextReader(XMLString, XmlNodeType.Document, d)
        Dim Count As Integer = 0
        Dim SBARNodeList As XmlNodeList = XLTree.GetElementsByTagName("SBAR") 'because; so

        While X.Read
            Dim Name As String = X.Name.ToString
            Dim Value As String = X.Value.ToString
            If X.NodeType = Xml.XmlNodeType.Element Then
                NList.Items.Add(Name.ToString)
                NVal.Items.Add("-")
            ElseIf X.NodeType = Xml.XmlNodeType.Text Then
                NList.Items.Add("-")
                NVal.Items.Add(Value.ToString)
            End If
        End While

        Count = NList.Items.Count - 1

        If SBARNodeList.Count = 0 Then
            Trap = 0
        ElseIf LCXMLTree.Contains("so") Then
            Trap = 1
        ElseIf LCXMLTree.Contains("because") Then
            Trap = 2
        Else
            Trap = 3
        End If

        Dim After As Boolean = False

        For El As Integer = Start To Count
            If Trap = 0 Then
                If NList.Items(El).Text = "DT" Then
                    If NList.Items(El + 2).Text = "JJ" Then
                        SelectedNN.Items.Add(NVal.Items(El + 3).Text.Trim & " " & NVal.Items(El + 5).Text.Trim) '<JJ>sore</JJ><NN>throat
                    Else
                        SelectedNN.Items.Add(NVal.Items(El + 3).Text.Trim) 'NN <JJ>sore</JJ><NN>throat
                    End If
                End If
                If NList.Items(El).Text = "CC" Then
                    SelectedNN.Items.Add(NVal.Items(El + 1).Text.Trim & "+")
                    SelectedNN.Items.Add(NVal.Items(El + 3).Text.Trim)
                End If
                If NList.Items(El).Text = "PRP1" Then
                    '<PRP1>my</PRP1><NNP>Canon</NNP><NN>camera</NN><NN>lens
                    If NList.Items(El + 2).Text = "NNP" Then
                        SelectedNN.Items.Add(NVal.Items(El - 2).Text.Trim & " " & _
                                             NVal.Items(El + 3).Text.Trim & " " & _
                                             NVal.Items(El + 5).Text.Trim & " " & _
                                             NVal.Items(El + 7).Text.Trim)
                    Else
                        'SelectedNN.Items.Add(NVal.Items(El - 2).Text.Trim) 'VB
                        'SelectedNN.Items.Add(NVal.Items(El + 3).Text.Trim) 'NN
                        SelectedNN.Items.Add(NVal.Items(El + 3).Text.Trim & " " & NVal.Items(El - 2).Text.Trim)
                    End If
                End If
            ElseIf Trap = 1 Then
                If NList.Items(El).Text = "SBAR" Then After = True
                If NList.Items(El).Text = "PRP1" And After = True Then
                    '<PRP1>my</PRP1><NNP>Canon</NNP><NN>camera</NN><NN>lens
                    If NList.Items(El + 2).Text = "NNP" Then
                        SelectedNN.Items.Add(NVal.Items(El - 2).Text.Trim & " " & _
                                             NVal.Items(El + 3).Text.Trim & " " & _
                                             NVal.Items(El + 5).Text.Trim & " " & _
                                             NVal.Items(El + 7).Text.Trim)
                    Else
                        'SelectedNN.Items.Add(NVal.Items(El - 2).Text.Trim) 'VB
                        'SelectedNN.Items.Add(NVal.Items(El + 3).Text.Trim) 'NN
                        SelectedNN.Items.Add(NVal.Items(El + 3).Text.Trim & " " & NVal.Items(El - 2).Text.Trim)
                    End If
                End If
                If NList.Items(El).Text = "DT" And After = True Then
                    If NList.Items(El + 2).Text = "JJ" Then
                        SelectedNN.Items.Add(NVal.Items(El + 3).Text.Trim & " " & NVal.Items(El + 5).Text.Trim) '<JJ>sore</JJ><NN>throat
                    Else
                        SelectedNN.Items.Add(NVal.Items(El + 3).Text.Trim) 'NN <JJ>sore</JJ><NN>throat
                    End If
                End If
                If NList.Items(El).Text = "CC" And After = True Then
                    SelectedNN.Items.Add(NVal.Items(El + 1).Text.Trim & "+")
                    SelectedNN.Items.Add(NVal.Items(El + 3).Text.Trim)
                End If
            ElseIf Trap = 2 Then
                If NList.Items(El).Text = "DT" Then
                    If NList.Items(El + 2).Text = "JJ" Then
                        SelectedNN.Items.Add(NVal.Items(El + 3).Text.Trim & " " & NVal.Items(El + 5).Text.Trim) '<JJ>sore</JJ><NN>throat
                    Else
                        SelectedNN.Items.Add(NVal.Items(El + 3).Text.Trim) 'NN <JJ>sore</JJ><NN>throat
                    End If
                End If
                If NList.Items(El).Text = "CC" Then
                    SelectedNN.Items.Add(NVal.Items(El + 1).Text.Trim & "+")
                    SelectedNN.Items.Add(NVal.Items(El + 3).Text.Trim)
                End If
                If NList.Items(El).Text = "PRP1" Then
                    '<PRP1>my</PRP1><NNP>Canon</NNP><NN>camera</NN><NN>lens
                    If NList.Items(El + 2).Text = "NNP" Then
                        SelectedNN.Items.Add(NVal.Items(El - 2).Text.Trim & " " & _
                                             NVal.Items(El + 3).Text.Trim & " " & _
                                             NVal.Items(El + 5).Text.Trim & " " & _
                                             NVal.Items(El + 7).Text.Trim)
                    Else
                        'SelectedNN.Items.Add(NVal.Items(El - 2).Text.Trim) 'VB
                        'SelectedNN.Items.Add(NVal.Items(El + 3).Text.Trim) 'NN
                        SelectedNN.Items.Add(NVal.Items(El + 3).Text.Trim & " " & NVal.Items(El - 2).Text.Trim)
                    End If
                End If
                If NList.Items(El).Text = "SBAR" Then Exit For
            End If
        Next

        Dim AppStrings As New clsAppStrings
        Count = SelectedNN.Items.Count - 1

        For ELF As Integer = Start To Count
            If SelectedNN.Items(ELF).Text <> "-" Then
                If SelectedNN.Items(ELF).Text.Contains("+") Then
                    Answer += vbNewLine & "UNION " & vbNewLine                    
                Else
                    Answer += AppStrings.SQLCommand
                    Answer += vbNewLine & "WHERE " & vbNewLine
                    Answer += "productline like '%" & SelectedNN.Items(ELF).Text & "%'" & vbNewLine
                End If
            End If
        Next

        Return Answer
    End Function
End Class



'Commented code that will not used in the execution.
'Answer += SelectedNN.Items(ELF).Text.Replace("+", "") & vbNewLine
'Dim AppStrings As New clsAppStrings
'Count = SelectedNN.Items.Count - 1

'Answer = AppStrings.SQLCommand
'Answer += vbNewLine & "WHERE " & vbNewLine 'where productline like '%car%'

'For ELF As Integer = Start To Count

'    If SelectedNN.Items(ELF).Text <> "-" Then
'        If SelectedNN.Items(ELF).Text.Contains("+") Then
'            Answer += SelectedNN.Items(ELF).Text.Replace("+", "") & vbNewLine
'        Else
'            Answer += "productline like '%" & SelectedNN.Items(ELF).Text & "%'" & vbNewLine
'        End If
'    End If
'Next


'Dim NodeList As XmlNodeList = XLTree.GetElementsByTagName("NN")
'Dim node As XmlNode = Nothing
'For Each node In NodeList
'    If SBARNodeList.Count = 0 Then 'because; so are not found
'        Answer += " " & node.InnerText
'    Else 'eather because or so are found
'        If LCXMLTree.Contains("so") Then
'            Answer = node.InnerText
'        ElseIf LCXMLTree.Contains("because") Then
'            Answer = node.InnerText
'            Exit For
'        Else
'            Answer += " " & node.InnerText
'        End If
'    End If
'Next
